﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace LANmine
{
    public partial class CateringForm : Form
    {
        private Language lmLang;
        private User lmUser;
        private Label lblCredits;
        
        /**komen uit de klasse itemData en worden hier opgevuld*/
        private String bestellingsnummer;

        private String tekstBestelling;
        private Int32 prijs;
        private String credits;

        private Int32 i;

        /**worden gebruikt bij het vullen van de vars, zie fill..()*/
        private String[] product_id;
        private String nummer;

        Form frmDetailsBestelling;

        public CateringForm(Language lmLang, User lmUser, Label lblCredits)
        {
            this.lmLang = lmLang;
            this.lmUser = lmUser;
            this.lblCredits = lblCredits;

            InitializeComponent();
            ApplyLanguage();
            loadProducten();
            loadOrders();
        }

        #region Language Specifications
        /**
         * MainForm::ApplyLanguage()
         * ---------------------------
         * Overwrite all text properties of all elements in all forms with the right ones.
         */
        private void ApplyLanguage()
        {
            /**
             * Header label
             */
            this.lblCatering.Text = this.lmLang.GetString("menu.btnCatering");
            /**
             * other labels & buttons
             */
            this.lblLstProducts.Text = this.lmLang.GetString("catering.lblLstProducts");
            this.lblLstOrders.Text = this.lmLang.GetString("catering.lblLstOrders");
            this.lblOrdersInProgress.Text = this.lmLang.GetString("catering.lblOrdersInProgress");
            this.btnOrder.Text = this.lmLang.GetString("catering.btnOrder");
            this.lblCredits.Text = this.lmLang.GetString("catering.lblCredits");
            this.tekstBestelling = this.lmLang.GetString("catering.tekstBestelling");
        }
        #endregion

        /**
         * Event Triggers
         */
        private void CateringForm_Resize(object sender, EventArgs e)
        {
            btnAddOne.Width = tlpNavProducts.Width;
            btnRemOne.Width = tlpNavProducts.Width;
            btnRemAll.Width = tlpNavProducts.Width;
        }

        private void CateringForm_ResizeEnd(object sender, EventArgs e)
        {
            btnAddOne.Width = tlpNavProducts.Width;
            btnRemOne.Width = tlpNavProducts.Width;
            btnRemAll.Width = tlpNavProducts.Width;
        }

        /**Verwijdert alle producten van je bestelling*/
        private void btnRemAll_Click(object sender, EventArgs e)
        {
            lbxBestellingen.Items.Clear();
        }
        
        // Zorgt ervoor dat er meteen een item wordt aangeduidt.
        private void CateringForm_Load(object sender, EventArgs e)
        {
            lbxProducten.SelectedItem = lbxProducten.Items[0];
        }

        private void btnAddOne_Click(object sender, EventArgs e)
        {
            lbxBestellingen.Items.Add(lbxProducten.SelectedItem.ToString());
        }

        private void btnRemOne_Click(object sender, EventArgs e)
        {
            if(lbxBestellingen.SelectedIndex != -1)
                lbxBestellingen.Items.RemoveAt(lbxBestellingen.SelectedIndex);
        }

        private void btnOrder_Click(object sender, EventArgs e)
        {
            /** het vullen van het product_id*/
            fillProductId();

            /** het vullen van het bestellingsnummer*/
            fillBestellingsnummer();

            /**De catering_bestelling_tabel opvullen*/
            Database dbBestelling = new Database("rw");
            SqlConnection dbConn = dbBestelling.getDbConn();

            SqlDataAdapter daBestelling = new SqlDataAdapter("SELECT product_id, deelnemer_id, bestellingsnummer FROM catering_bestellingen b", dbConn);
            
            DataSet dsBestelling = new DataSet();
            DataTable dtBestelling = new DataTable("Bestelling");
            daBestelling.Fill(dtBestelling);

            dsBestelling.Tables.Add(dtBestelling);
            DataRow drCurrent;


            dbConn.Open();
            try
            {
                /**De verschillende bestede producten toevoegen aan de datatable*/
                i = 0;
                foreach (object o in lbxBestellingen.Items)
                {
                    drCurrent = dtBestelling.NewRow();
                    drCurrent["product_id"] = Convert.ToInt32(product_id[i]);
                    drCurrent["deelnemer_id"] = Convert.ToInt32(lmUser.ID);
                    drCurrent["bestellingsnummer"] = (Convert.ToInt32(nummer) + 1);
                    dtBestelling.Rows.Add(drCurrent);
                    drCurrent = null;
                    i++;
                }
                /**de insert die  moet gebeuren bepalen en opvullen*/
                string sqlInsert = "INSERT INTO catering_bestellingen (product_id, deelnemer_id, bestellingsnummer) VALUES (@product_id, @deelnemer_id, @bestellingsnummer)";

                SqlCommand cmdInsert = new SqlCommand(sqlInsert, dbConn);
                cmdInsert.Parameters.Add("@product_id", SqlDbType.Int, 4, "product_id");
                cmdInsert.Parameters.Add("@deelnemer_id", SqlDbType.Int, 4, "deelnemer_id");
                cmdInsert.Parameters.Add("@bestellingsnummer", SqlDbType.Int, 4, "bestellingsnummer");

                daBestelling.InsertCommand = cmdInsert;

                /**de totale prijs berekenen van de bestelling*/
                berekenPrijsEnCredits();
                
                /**Zowel de bestelling inserten in de database als de bestelling ond e onderste listbox toevoegen en de credits van de deelnemer aanpassen*/
                if (Convert.ToInt32(prijs) <= Convert.ToInt32(credits))
                {
                    daBestelling.Update(dsBestelling, "Bestelling");
                    addOrder();
                    changeCredits();
                }
                else
                {
                    MessageBox.Show(tekstBestelling);
                }

                /**de listbox met de bestelling leegmaken*/
                lbxBestellingen.Items.Clear();
                  
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
            finally
            {
                dbConn.Close();
            }
        }

        private void loadProducten()
        {
            try
            {
                Database dbProducten = new Database("ro");
                SqlConnection dbConn = dbProducten.getDbConn();
                
                SqlDataAdapter daProducten = new SqlDataAdapter("Select * From catering_producten ORDER BY naam", dbConn);
                DataSet dsProducten = new DataSet("Product");

                daProducten.FillSchema(dsProducten, SchemaType.Source, "Product");
                daProducten.Fill(dsProducten, "Product");

                DataTable dtProducten;
                dtProducten = dsProducten.Tables["Product"];

                foreach (DataRow drCurrent in dtProducten.Rows)
                {
                    lbxProducten.Items.Add(drCurrent["naam"].ToString() + " " + drCurrent["prijs"].ToString());

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
        }

        private void fillProductId() 
        {
            Database dbBestelling = new Database("ro");
            SqlConnection dbConn = dbBestelling.getDbConn();

            product_id = new String[lbxBestellingen.Items.Count + 1];
            for (i = 0; i < lbxBestellingen.Items.Count; i++)
            {
                SqlDataAdapter daProduct = new SqlDataAdapter("SELECT id FROM catering_producten WHERE naam LIKE '" + lbxBestellingen.Items[i].ToString().Substring(0, 5) + "%'", dbConn);

                DataTable dtProduct = new DataTable("Product");
                daProduct.Fill(dtProduct);

                product_id[i] = dtProduct.Rows[0]["id"].ToString();
            }
        }

        private void fillBestellingsnummer() 
        {
            Database dbBestelling = new Database("ro");
            SqlConnection dbConn = dbBestelling.getDbConn();

            SqlDataAdapter daBestellingsnummer = new SqlDataAdapter("SELECT TOP 1 bestellingsnummer FROM catering_bestellingen ORDER BY bestellingsnummer desc", dbConn);
            
            DataTable dtBestellingsnummer = new DataTable("Bestelling");
            daBestellingsnummer.Fill(dtBestellingsnummer);
            if (dtBestellingsnummer.Rows.Count != 0)
                nummer = dtBestellingsnummer.Rows[0]["Bestellingsnummer"].ToString();
            else
                nummer = "0";
        }

        private void berekenPrijsEnCredits() 
        {
            /**De prijs die je bestelling zal kosten*/
            Database dbPrijs = new Database("rw");
            SqlConnection dbConn = dbPrijs.getDbConn();
            
            prijs = 0;
            i = 0;
            foreach (Object o in lbxBestellingen.Items)
            {
                SqlDataAdapter daPrijs= new SqlDataAdapter("SELECT prijs FROM catering_producten WHERE naam LIKE '" + lbxBestellingen.Items[i].ToString().Substring(0, 5) + "%'",dbConn);
            
                DataTable dtPrijs = new DataTable("Prijs");
                daPrijs.Fill(dtPrijs);

                prijs = prijs + Convert.ToInt32(dtPrijs.Rows[0]["prijs"].ToString());
                i++;
            }

            /**De credtis die je nog bezit, zelfs indien aangepast in adminform*/
            SqlDataAdapter daCredits = new SqlDataAdapter("SELECT credits FROM deelnemers WHERE id = " + lmUser.ID, dbConn);
            DataTable dtCredits = new DataTable("deelnemers");
            daCredits.Fill(dtCredits);

            credits = dtCredits.Rows[0]["credits"].ToString();
        }
        
        private void addOrder() 
        {
            Database dbOrder = new Database("rw");
            SqlConnection dbConn = dbOrder.getDbConn();
            SqlDataAdapter daOrder;

            daOrder = new SqlDataAdapter("SELECT b.bestellingsnummer, SUM(p.prijs) as prijs FROM catering_bestellingen b INNER JOIN catering_producten p ON p.id = b.product_id where b.bestellingsnummer =  " + (Int32.Parse(nummer) + 1) + " GROUP BY b.bestellingsnummer", dbConn);
            
            DataSet dsOrder = new DataSet("Order");
            daOrder.Fill(dsOrder, "Orders");

            DataTable dtOrder;
            dtOrder = dsOrder.Tables["Orders"];
            foreach(DataRow r in dtOrder.Rows)
                lbxOIProgress.Items.Add(new ItemData(r["bestellingsnummer"].ToString(), (this.lmLang.GetString("catering.bestellingsnummer") + " " + r["bestellingsnummer"].ToString() + " " + this.lmLang.GetString("catering.prijs") + " " + r["prijs"].ToString())));
        }

        private void loadOrders()
        {
            try
            {
                Database dbOrders = new Database("ro");
                SqlConnection dbConn = dbOrders.getDbConn();
                
                SqlDataAdapter daOrders = new SqlDataAdapter("SELECT b.bestellingsnummer, SUM(p.prijs) as prijs FROM catering_bestellingen b INNER JOIN catering_producten p ON p.id = b.product_id WHERE b.deelnemer_id = " + lmUser.ID + " GROUP BY b.bestellingsnummer" , dbConn);
                DataSet dsOrders = new DataSet("Orders");

                daOrders.Fill(dsOrders, "Orders");

                DataTable dtOrders;
                dtOrders = dsOrders.Tables["Orders"];

                foreach (DataRow r in dtOrders.Rows)
                {
                    lbxOIProgress.Items.Add(new ItemData(r["bestellingsnummer"].ToString(), (this.lmLang.GetString("catering.bestellingsnummer") + " " + r["bestellingsnummer"].ToString() + " " + this.lmLang.GetString("catering.prijs") + " " + r["prijs"].ToString())));
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
        }

        private void changeCredits() 
        {
            Database dbCredits = new Database("rw");
            SqlConnection dbConn = dbCredits.getDbConn();

            SqlDataAdapter daCredits = new SqlDataAdapter("SELECT credits FROM deelnemers WHERE id = " + lmUser.ID, dbConn);

            DataSet dsCredits = new DataSet();
            DataTable dtCredits = new DataTable("Credits"); ;
            daCredits.Fill(dtCredits);

            dsCredits.Tables.Add(dtCredits);
            foreach (DataRow r in dtCredits.Rows) 
            {
                r["credits"] = Convert.ToInt32(r["credits"]) - prijs;
            }

            SqlCommand cmdUpdate = new SqlCommand("UPDATE deelnemers SET credits = @credits WHERE id = " + lmUser.ID, dbConn);
            cmdUpdate.Parameters.Add("@credits", SqlDbType.Int, 4, "credits");
            daCredits.UpdateCommand = cmdUpdate;
            
            daCredits.Update(dsCredits, "Credits");

            SqlDataAdapter daCredit = new SqlDataAdapter("SELECT credits FROM deelnemers WHERE id = " + lmUser.ID, dbConn);
            DataTable dtCredit = new DataTable("Credit");
            daCredit.Fill(dtCredit);
            this.lblCredits.Text = this.lmLang.GetString("catering.lblCredits") + " " + dtCredit.Rows[0]["Credits"].ToString();
        }

        private void lbxOIProgress_DoubleClick(object sender, EventArgs e)
        {
            this.frmDetailsBestelling = new DetailsBestellingForm(lmLang, bestellingsnummer);
            this.frmDetailsBestelling.Text = "Details van de bestellingen";
            this.frmDetailsBestelling.ShowDialog();
        }

        private void lbxOIProgress_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.bestellingsnummer = ((ItemData)lbxOIProgress.SelectedItem).itemdata.ToString();
        }

    }
}

